﻿Imports System.Reflection
Imports System.Web.UI
Imports System.Web.UI.WebControls
Imports System.IO
Imports OfficeOpenXml
Imports System.Web
Imports System.Net.Mail

Public Class myUtilities

    Public Function CreateDataTableColumns(ByVal classInstanceName As Object) As DataTable
        Dim dt As New DataTable

        Dim classType As Type = classInstanceName.GetType()

        Dim classProperties As PropertyInfo() = classType.GetProperties()

        For Each itm In classProperties
            dt.Columns.Add(itm.Name, itm.PropertyType)
        Next

        Return dt

    End Function

    Public Sub ExportToExcelFromGridView(ByVal filename As String, ByVal gridviewDataSource As GridView)
        ' Try


        Dim dt As New DataTable
        Dim grdheaderrow As GridViewRow = gridviewDataSource.HeaderRow

        'create the header
        For Each itemcell As TableCell In grdheaderrow.Cells
            dt.Columns.Add(itemcell.Text, Type.GetType("System.String"))
        Next

        For Each grdrow As GridViewRow In From grdrow1 As GridViewRow In gridviewDataSource.Rows Where grdrow1.RowType = DataControlRowType.DataRow
            dt.Rows.Add() 'creates an empty row in the datatable
            For i As Integer = 0 To grdrow.Cells.Count - 1
                dt.Rows(grdrow.RowIndex)(i) = grdrow.Cells(i).Text
            Next
        Next


        Dim colunmCount = dt.Columns.Count

        Dim ms As New MemoryStream()

        Using exPackage As New ExcelPackage(ms)


            Dim wb = exPackage.Workbook
            Dim ws = wb.Worksheets.Add(filename)

            'load the worksheet cells from the datatable
            ws.Cells.LoadFromDataTable(dt, True)

            'autofit each column
            For i As Integer = 1 To colunmCount
                ws.Column(i).AutoFit()
            Next
            'for (int i = 1; i <= colunmCount; i++)
            '{
            '    ws.Column(i).AutoFit();
            '}


            exPackage.SaveAs(ms)


            HttpContext.Current.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
            HttpContext.Current.Response.AddHeader(String.Format("content-disposition", "attachment;  filename={0}.xlsx"), filename)

            ms.WriteTo(HttpContext.Current.Response.OutputStream)
            HttpContext.Current.Response.Flush()
            HttpContext.Current.Response.End()




        End Using

        'Catch ex As Exception

        'End Try

    End Sub

    Public Function ConvertToCurrency(ByVal amount As String) As String
        Return FormatNumber(amount, 2, , , TriState.True)
    End Function

    Public Sub DownloadFile(ByVal filepath As String)

        Dim filename As String = Path.GetFileName(HttpContext.Current.Server.MapPath(filepath))
        HttpContext.Current.Response.ContentType = "application/octet-stream"
        HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + filename)
        HttpContext.Current.Response.TransmitFile(HttpContext.Current.Server.MapPath(filePath))

        HttpContext.Current.Response.End()
    End Sub

    Public Sub SendMailOut(ByVal mailfrom As String, ByVal mailto() As String, ByVal displayName As String, ByVal mailSubject As String, ByVal mailBody As String, ByVal smtpHostSetting As String, ByVal smtpPort As Integer, ByVal smtpUsername As String, ByVal smtpPassword As String, Optional ByVal mailReplyTo As String = Nothing, Optional ByVal mailAddressCc() As String = Nothing, Optional ByVal mailAddressBcc As String = Nothing)

        Dim mailmessage As New MailMessage
        mailmessage.From = New MailAddress(mailfrom, displayName)

        For Each item As String In mailto
            mailmessage.To.Add(New MailAddress(item))
        Next

        If Not IsNothing(mailAddressCc) Then
            For Each mailCc As String In mailAddressCc
                mailmessage.CC.Add(New MailAddress(mailCc))
            Next
        End If
        If Not IsNothing(mailAddressBcc) Then
            ' For Each mailCc As String In mailAddressCc
            mailmessage.Bcc.Add(New MailAddress(mailAddressBcc))
            'Next
        End If
        If Not IsNothing(mailReplyTo) Then

            mailmessage.ReplyToList.Add(New MailAddress(mailReplyTo))

        End If

        mailmessage.Subject = mailSubject
        mailmessage.Body = mailBody
        mailmessage.IsBodyHtml = True


        Dim smtpsettings As New SmtpClient(smtpHostSetting, smtpPort)
        smtpsettings.EnableSsl = True
        smtpsettings.Credentials = New Net.NetworkCredential(smtpUsername, smtpPassword)

        smtpsettings.Send(mailmessage)



    End Sub
End Class
